﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Excel = Microsoft.Office.Interop.Excel;
using Missing = System.Reflection.Missing;
using System.IO;


public partial class WebUserControl_ControlReportInMonthCustomerBirthday : System.Web.UI.UserControl
{
   
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    protected void btReport_Click(object sender, EventArgs e)
    {
        string stroreName = "exec [K_getInMonthBirthdayCustomer] '" + DropMonth.SelectedItem.ToString() + "'"; 
        string connection = "OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;" + BCDataCenter.Configuration.CurrentConnectionString;

        if (stroreName != "")
        {
            //KIỂM TRA FILE CÓ TỒN TẠI HAY KHÔNG,NẾU CÓ THÌ XÓA
            if (File.Exists(Server.MapPath("BaoCaoSinhNhatKhachHang.xls")))
            {
                File.Delete(Server.MapPath("BaoCaoSinhNhatKhachHang.xls"));
            }

            string cmd = stroreName;
            Excel.Application excel = new Excel.Application();
            try
            {
                excel.Visible = false;
                excel.AlertBeforeOverwriting = false;
                Excel.Workbook workbook = excel.Workbooks.Add(Missing.Value);
                Excel.Worksheet sheet = (Excel.Worksheet)workbook.Worksheets.Add(Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                Excel.Style style = workbook.Styles.Add("Style1", Missing.Value);
                style.Font.Bold = true;
                style.Font.Size = 32;
                sheet.Activate();
                Excel.Range range = ((Excel.Range)sheet.Cells[1, 1]);
                range.Style = style;
                range.Value2 = "BÁO CÁO THÁNG CÓ SINH NHẬT KHÁCH HÀNG" + DropMonth.SelectedItem.ToString();
                Excel.Range range1 = ((Excel.Range)sheet.Cells[2, 1]);
                range1.Value2 = "Ngày giờ lấy báo cáo :" + DateTime.Now.ToString();
                Excel.PivotCache pivotCache = excel.ActiveWorkbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlExternal, Missing.Value);
                //GÁN CHUỔI KẾT NỐI
                pivotCache.Connection = connection;
                pivotCache.MaintainConnection = true;
                //GÁN CÂU LỆNH QUERY
                pivotCache.CommandText = cmd;
                pivotCache.CommandType = Excel.XlCmdType.xlCmdSql;
                Excel.PivotTables pivotTables = (Excel.PivotTables)sheet.PivotTables(Missing.Value);
                Excel.PivotTable pivotTable = pivotTables.Add(pivotCache, excel.ActiveCell[20, 1], "PivotTable11", Missing.Value, Missing.Value);

                object[] noSub = { false, false, false, false, false, false, false, false, false, false, false, false };

                //ĐẶT TẠI VÙNG PAGE
                Excel.PivotField pageField17 = (Excel.PivotField)pivotTable.PivotFields("lastbydate");
                pageField17.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField17.Caption = "Ngày mua hàng lần gần nhất";

                Excel.PivotField pageField18 = (Excel.PivotField)pivotTable.PivotFields("firstbydate");
                pageField18.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField18.Caption = "Ngày mua hàng lần đầu";

                Excel.PivotField pageField19 = (Excel.PivotField)pivotTable.PivotFields("sitename");
                pageField19.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField19.Caption = "VP mua hàng lần đầu";

                Excel.PivotField pageField20 = (Excel.PivotField)pivotTable.PivotFields("phone");
                pageField20.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField20.Caption = "Số điện thoại";


                Excel.PivotField pageField21 = (Excel.PivotField)pivotTable.PivotFields("salesp_name");
                pageField21.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField21.Caption = "Tên TVV";


                Excel.PivotField pageField22 = (Excel.PivotField)pivotTable.PivotFields("salesp_key");
                pageField22.Orientation = Excel.XlPivotFieldOrientation.xlPageField;
                pageField22.Caption = "Mã TVV";


                //ĐẶT TẠI VÙNG DATA
                Excel.PivotField pageField23 = (Excel.PivotField)pivotTable.PivotFields("vol");
                pageField23.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
                pageField23.Caption = "DS đến thời điểm lấy báo cáo";


                //ĐẶT TẠI VÙNG ROW
                Excel.PivotField pageField24 = (Excel.PivotField)pivotTable.PivotFields("STT");
                pageField24.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField24.Caption = "Số TT";
                pageField24.set_Subtotals(Missing.Value, noSub);

                Excel.PivotField pageField25 = (Excel.PivotField)pivotTable.PivotFields("customer_key");
                pageField25.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField25.Caption = "Mã KH";
                pageField25.set_Subtotals(Missing.Value, noSub);

                Excel.PivotField pageField26 = (Excel.PivotField)pivotTable.PivotFields("customer_name");
                pageField26.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField26.Caption = "Tên KH";
                pageField26.set_Subtotals(Missing.Value, noSub);

                Excel.PivotField pageField27 = (Excel.PivotField)pivotTable.PivotFields("Birthday");
                pageField27.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField27.Caption = "Ngày sinh";
                pageField27.set_Subtotals(Missing.Value, noSub);

                Excel.PivotField pageField28 = (Excel.PivotField)pivotTable.PivotFields("address");
                pageField28.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
                pageField28.Caption = "Địa chỉ";
                pageField28.set_Subtotals(Missing.Value, noSub);

                workbook.SaveAs(Server.MapPath("BaoCaoSinhNhatKhachHang.xls"), Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                workbook.Close(true, Missing.Value, Missing.Value);
                excel.Quit();

                saveFile(this.Response, Server.MapPath("BaoCaoSinhNhatKhachHang.xls"));
            }
            catch (Exception ex)
            {
                ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "thongbao", "alert('" + ex.Message + "')", true);
            }
            finally
            {
                // Make sure we release the reference to the underlying COM object
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
            }

        }
    }
    void saveFile(System.Web.HttpResponse page, string PathfileName)
    {
        string filename = PathfileName;
        if (filename != "")
        {
            string path = filename;
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            if (file.Exists)
            {
                page.Clear();
                page.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                page.AddHeader("Content-Length", file.Length.ToString());
                page.ContentType = "application/octet-stream";
                page.WriteFile(file.FullName);
                page.End();

            }
        }

    }
}
